package util;

import anna.ExcelConfig;
import anna.ExcelField;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;

/**
 * Excel导入导出工具类
 *
 * @author qinxuekui
 * @version 1.0
 * 2022-03-24 16:29
 */
public class CodeExcelUtil {

    // 临时目录
    private final static String BASE_FILE_PATH = System.getProperty("user.dir") + File.separator + "temp1" + File.separator;
    // 时间格式转换 (防止多线程异常)
    private final static ThreadLocal<SimpleDateFormat> dateFormatSSS = ThreadLocal.withInitial(() -> new SimpleDateFormat("yyyyMMddHHmmssSSS"));

    // todo ： nacos?
    private final static int CONNECT_TIMEOUT_LONG = 2 * 60 * 1000;
    private final static int READ_TIMEOUT_LONG = 2 * 60 * 1000;

    /**
     * 防止实例化
     */
    private CodeExcelUtil() {
    }

    /**
     * 集合 -> Excel文件
     *
     * @param listBean 对象集合 （需要在类和字段分别加上@ExcelConfig和@ExcelField注解)
     */
    public static File list2File(List<?> listBean) throws Exception {
        if (CollectionUtils.isEmpty(listBean)) {
            throw new Exception("listData is Empty");
        }

        // 生成Excel文件
        Class<?> aClass = listBean.get(0).getClass();
        ExcelConfig excelConfig = aClass.getAnnotation(ExcelConfig.class);
        String filePath = BASE_FILE_PATH + excelConfig.fileName();
        if (excelConfig.nameSuffix()) {
            filePath += dateFormatSSS.get().format(new Date());
        }
        File file = new File(filePath + ".xlsx");
        BigExcelWriter writer = ExcelUtil.getBigWriter(file);
        // 设置别名
        doAddAlias(listBean, writer);
        // 一次性写出内容，使用默认样式
        writer.write(listBean);
        // 关闭writer，释放内存
        writer.close();

        return file;
    }

    /**
     * Excel文件 -> List对象
     * 默认读取第0个sheet
     *
     * @param file  Excel文件
     * @param calzz 泛型
     * @throws Exception 调用者需处理异常
     */
    public static <T> List<T> file2List(File file, Class<T> calzz) throws Exception {
        // 读取Excel文件
        ExcelReader reader = ExcelUtil.getReader(file, 0);
        List<Map<String, Object>> dataList = reader.readAll();
        reader.close();
        // 遍历读取到的数据转换成对应的泛型
        if (!CollectionUtils.isEmpty(dataList)) {
            Excel excel = readExcelConfig(calzz);
            return file2ListCore(dataList, excel.fieldsConfig, calzz);
        }
        return new ArrayList<>();
    }

    /**
     * Excel文件 -> List对象
     * 默认读取第0个sheet
     *
     * @param file  Excel文件
     * @param calzz 泛型
     * @throws Exception 调用者需处理异常
     */
    public static <T> List<T> file2List(InputStream inputStream, Class<T> calzz) throws Exception {
        // 读取Excel文件
        ExcelReader reader = ExcelUtil.getReader(inputStream, 0);
        List<Map<String, Object>> dataList = reader.readAll();
        reader.close();
        // 遍历读取到的数据转换成对应的泛型
        if (!CollectionUtils.isEmpty(dataList)) {
            Excel excel = readExcelConfig(calzz);
            return file2ListCore(dataList, excel.fieldsConfig, calzz);
        }
        return new ArrayList<>();
    }

    /**
     * Excel文件 -> List对象
     * 默认读取第0个sheet
     *
     * @param fileUrl Excel文件URL
     * @param calzz   泛型
     * @throws Exception 调用者需处理异常
     */
    public static <T> List<T> file2List(String fileUrl, Class<T> calzz) throws Exception {
        // 读取Excel文件
        List<Map<String, Object>> dataList = downLoad(fileUrl);
        // 遍历读取到的数据转换成对应的泛型
        if (!CollectionUtils.isEmpty(dataList)) {
            Excel excel = readExcelConfig(calzz);
            return file2ListCore(dataList, excel.fieldsConfig, calzz);
        }
        return new ArrayList<>();
    }

    /**
     * 设置表头别名
     *
     * @param list        数据集合
     * @param excelWriter hutool工具类
     */
    public static <T> void doAddAlias(List<T> list, ExcelWriter excelWriter) {
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        Class<?> aClass = list.get(0).getClass();
        Field[] declaredFields = getAllFieldArray(aClass);
        Arrays.stream(declaredFields).filter(a -> a.isAnnotationPresent(ExcelField.class)).sorted((k1, k2) -> {
            int sort1 = k1.getAnnotationsByType(ExcelField.class)[0].index();
            int sort2 = k2.getAnnotationsByType(ExcelField.class)[0].index();
            return sort1 - sort2;
        }).forEach(b -> {
            ExcelField exportProperty = b.getAnnotationsByType(ExcelField.class)[0];
            excelWriter.addHeaderAlias(b.getName(), exportProperty.value());
        });
    }

    /**
     * 读取URL地址的文件流，转为Excel二维数据
     *
     * @param pathUrl Excel文件超链接
     * @return List<Map < String, Object>>
     * @throws Exception
     */
    public static List<Map<String, Object>> downLoad(String pathUrl) {
        InputStream inputStream = null;
        ExcelReader reader = null;

        try {
            if (StringUtils.isEmpty(pathUrl)) {
                return null;
            }


            URL urlTemp = new URL(pathUrl);
            HttpURLConnection urlCon = (HttpURLConnection)urlTemp.openConnection();
            urlCon.setConnectTimeout(CONNECT_TIMEOUT_LONG);
            urlCon.setReadTimeout(READ_TIMEOUT_LONG);
            inputStream  = urlCon.getInputStream();
            reader = ExcelUtil.getReader(inputStream, 0);
            List<Map<String, Object>> dataList = reader.readAll();
            return dataList;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (null != inputStream) {
                try {
                    inputStream.close();
                } catch (Exception e) {

                }
            }
            if (null != reader) {
                try {
                    reader.close();
                } catch (Exception e) {

                }

            }
        }
    }

    public static File list2File2(List<?> listBean, String filePath) throws Exception {
        if (CollectionUtils.isEmpty(listBean)) {
            throw new Exception("listData is Empty");
        }
        // Excel注解配置
        Excel excel = readExcelConfig(listBean.get(0).getClass());
        // 预设导出数据
        List<List<?>> rows = new ArrayList<>();
        // 读取表头
        List<Object> excelTitle = getTitle(excel.fieldsConfig   );
        // 转换内容数据
        List<List<?>> datas = convertDataList(listBean, excel, excelTitle);
        // 构造数据
        rows.add(excelTitle);
        rows.addAll(datas);
        // 生成Excel文件
        File file = new File(filePath);
        BigExcelWriter writer = ExcelUtil.getBigWriter(file);
        // 一次性写出内容，使用默认样式
        writer.write(rows);
        // 关闭writer，释放内存
        writer.close();

        return file;
    }

    /**
     * 集合 -> Excel文件(index为绝对定位支持index,例如index可以为 0,3,7)
     *
     * @param listBean 对象集合 （需要在类和字段分别加上@ExcelConfig和@ExcelField注解)
     */
    public static File list2File2(List<?> listBean) throws Exception {
        if (CollectionUtils.isEmpty(listBean)) {
            throw new Exception("listData is Empty");
        }
        // Excel注解配置
        Excel excel = readExcelConfig(listBean.get(0).getClass());
        // 预设导出数据
        List<List<?>> rows = new ArrayList<>();
        // 读取表头
        List<Object> excelTitle = getTitle(excel.fieldsConfig   );
        // 转换内容数据
        List<List<?>> datas = convertDataList(listBean, excel, excelTitle);
        // 构造数据
        rows.add(excelTitle);
        rows.addAll(datas);
        // 生成Excel文件
        String filePath = BASE_FILE_PATH + excel.fileName;
        if (excel.nameSuffix) {
            filePath += dateFormatSSS.get().format(new Date());
        }
        File file = new File(filePath + ".xlsx");
        BigExcelWriter writer = ExcelUtil.getBigWriter(file);
        // 一次性写出内容，使用默认样式
        writer.write(rows);
        // 关闭writer，释放内存
        writer.close();

        return file;
    }

    /**
     * 根据注解配置将dataList转换成对应的泛型数据
     *
     * @param dataList        Excel原数据
     * @param fieldsConfigMap 字段注解配置
     * @param calzz           泛型类
     * @throws Exception 调用者需处理异常
     */
    private static <T> List<T> file2ListCore(List<Map<String, Object>> dataList, Map<String, Map<String, Object>> fieldsConfigMap, Class<T> calzz) throws Exception {
        // 预设返回结果
        List<T> resultList = new ArrayList<>();
        // 减少内存占用,声明公用变量
        T obj = null;
        String titleName, fieldName = null;
        Object fieldValue = null;

        Set<String> keys = fieldsConfigMap.keySet();
        // 遍历转换数据
        for (Map<String, Object> itemMap : dataList) {
            obj = calzz.newInstance();
            for (Map.Entry<String, Object> entry : itemMap.entrySet()) {
                titleName = transformFiled(keys,entry.getKey());
                fieldValue = entry.getValue();
                Map<String, Object> fieldConfig = fieldsConfigMap.get(titleName);
                // 判断不为空才获取
                if (!(fieldConfig == null || fieldConfig.isEmpty())) {
                    fieldName = (String) fieldConfig.get("field");
                }
                if (fieldValue != null && fieldName != null) {
                    try {
                        ReflectUtil.setFieldValue(obj, fieldName, fieldValue);
                    } catch(Exception e) {
                        //e.printStackTrace();
                    }

                }
            }
            resultList.add(obj);
        }

        return resultList;
    }

    public static String transformFiled(Set<String> key,String source){
        if (StringUtils.isEmpty(key) || StringUtils.isEmpty(source)){
            return source;
        }
        String transForm = key.stream().filter(a -> source.indexOf(a) != -1).findFirst().orElse(null);

        return StringUtils.isEmpty(transForm)?transForm:source;
    }








    private static Excel readExcelConfig(Class<?> clazz) throws Exception {
        // 判断类是否存在@ExcelConfig注解
        if (!clazz.isAnnotationPresent(ExcelConfig.class)) {
            throw new Exception(clazz.toString() + "There is no ExcelConfig.class config");
        }
        ExcelConfig excelConfig = clazz.getAnnotation(ExcelConfig.class);
        // 读取Excel属性 
        Map<String, Map<String, Object>> fieldsConfigMap = getFieldsConfigMap(clazz);

        // 返回结果
        return new Excel(excelConfig.fileName(), excelConfig.nameSuffix(), fieldsConfigMap);
    }

    /**
     * 获取字段的Excel配置（如果没配置不抛异常）
     *
     * @param clazz 泛型
     * @return
     */
    private static Map<String, Map<String, Object>> getFieldsConfigMap(Class<?> clazz) {
        // 预设返回结果
        Map<String, Map<String, Object>> fieldsConfigMap = new HashMap<>();

        List<Field> fieldsList = getAllField(clazz);
        // 遍历字段获取注解上的配置
        for (Field field : fieldsList) {
            if (field.isAnnotationPresent(ExcelField.class)) {
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                Map<String, Object> fieldProperty = new HashMap<>();
                fieldProperty.put("index", excelField.index());
                fieldProperty.put("field", field.getName());
                fieldProperty.put("value", excelField.value());

                fieldsConfigMap.put(excelField.value(), fieldProperty);
            }
        }

        return fieldsConfigMap;
    }

    /**
     * 防止list.set(index,value) 出现异常，对新创建的list做一个初始化
     *
     * @param fieldsConfig
     * @return
     */
    private static List<Object> initListByFieldsConfig(Map<String, Map<String, Object>> fieldsConfig) {
        //获取最大列的下标
        AtomicReference<Integer> maxIndex = new AtomicReference<>(0);
        fieldsConfig.forEach((k, v) -> {
            Integer index = Integer.valueOf(v.get("index").toString());
            if (index > maxIndex.get()) {
                maxIndex.set(index);
            }
        });

        List<Object> tempList = new ArrayList<>();
        for (int i = 0; i <= maxIndex.get(); i++) {
            tempList.add(null);
        }
        return tempList;
    }

    private static List<List<?>> convertDataList(List<?> listBean, Excel excel, List<Object> excelTitle) {
        // 预设结果
        List<List<?>> rows = new ArrayList<>();
        for (Object obj : listBean) {
            // field属性 index 下标，value表头名，field字段名
            Map<String, Map<String, Object>> fieldsConfig = excel.fieldsConfig;
            // 根据注解顺序获取值
            List<Object> tempList = initListByFieldsConfig(fieldsConfig);

            for (Object o : excelTitle) {
                if (o == null) {
                    // 跳过没有配置的列
                    continue;
                }
                Map<String, Object> fieldConfig = fieldsConfig.get(o.toString());
                String fieldName = fieldConfig.get("field").toString();
                Object value = ReflectUtil.getFieldValue(obj, fieldName);
                Integer index = Integer.valueOf(fieldConfig.get("index").toString());
                tempList.set(index, value);
            }
            rows.add(tempList);
        }
        return rows;
    }

    /**
     * 根据注解配置读取第一行的表头
     *
     * @param fieldConfig
     * @return
     */
    private static List<Object> getTitle(Map<String, Map<String, Object>> fieldConfig) throws Exception {
        if (fieldConfig == null || fieldConfig.isEmpty()) {
            throw new Exception("fieldConfig is Empty");
        }
        List<Object> title = initListByFieldsConfig(fieldConfig);
        fieldConfig.forEach((k, v) -> title.add(null));


        // 按照注解配置读取表头,顺序根据index属性
        for (Map.Entry<String, Map<String, Object>> entry : fieldConfig.entrySet()) {
            Map<String, Object> fieldItemMap = entry.getValue();
            Integer excelIndex = Integer.valueOf(fieldItemMap.get("index").toString());
            title.set(excelIndex, entry.getKey());
        }
        for (int i = 0; i < title.size(); i++) {
            if (title.get(i) == null){
                title.remove(i);
                i--;
            }
        }

        return title;
    }

    /**
     * 反射获取类的全部字段（包含父类）
     *
     * @param clazz 类
     * @return List<Field>
     */
    private static List<Field> getAllField(Class<?> clazz) {
        // 读取字段属性
        List<Field> fieldsList = new ArrayList<>();
        // 遍历所有父类字节码对象
        while (clazz != null) {
            // 获取字节码对象的属性对象数组
            Field[] declaredFields = clazz.getDeclaredFields();
            fieldsList.addAll(Arrays.asList(declaredFields));
            // 获得父类的字节码对象
            clazz = clazz.getSuperclass();
        }

        return fieldsList;
    }

    /**
     * 反射获取类的全部字段（包含父类）
     *
     * @param clazz 类
     * @return List<Field>
     */
    private static Field[] getAllFieldArray(Class<?> clazz) {
        // 读取字段属性
        Field[] resultArray = new Field[0];
        // 遍历所有父类字节码对象
        while (clazz != null) {
            // 获取字节码对象的属性对象数组
            Field[] declaredFields = clazz.getDeclaredFields();
            resultArray = concat(resultArray, declaredFields);
            // 获得父类的字节码对象
            clazz = clazz.getSuperclass();
        }

        return resultArray;
    }

    public static <t> t[] concat(t[] first, t[] second) {
        t[] result = Arrays.copyOf(first, first.length + second.length);
        System.arraycopy(second, 0, result, first.length, second.length);
        return result;
    }

    /**
     * Excel属性描述对象
     */
    static class Excel {
        // Excel文件名称
        String fileName;
        // 是否追加后缀
        boolean nameSuffix;
        // field属性 index 下标，value表头名，field字段名
        Map<String, Map<String, Object>> fieldsConfig;

        public Excel() {
        }

        public Excel(String fileName, boolean nameSuffix, Map<String, Map<String, Object>> fieldsConfig) {
            this.fileName = fileName;
            this.nameSuffix = nameSuffix;
            this.fieldsConfig = fieldsConfig;
        }
    }

    public static void main(String[] args) throws Exception {

    }

}

